#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库一致性检查脚本
检查模型定义与实际数据库结构的一致性
"""

import sys
import os
sys.path.append('/home/gd/dispatch-system/backend')

from wsgi import create_app
from app.models import db
from sqlalchemy import inspect, text
import json

def check_database_consistency():
    """检查数据库一致性"""
    app = create_app()
    
    with app.app_context():
        inspector = inspect(db.engine)
        
        print("=" * 60)
        print("数据库一致性检查报告")
        print("=" * 60)
        
        # 1. 检查表是否存在
        print("\n1. 表存在性检查:")
        print("-" * 30)
        
        # 从模型中获取所有表名
        model_tables = set(db.metadata.tables.keys())
        # 从数据库中获取所有表名
        db_tables = set(inspector.get_table_names())
        
        print(f"模型定义的表 ({len(model_tables)}): {sorted(model_tables)}")
        print(f"数据库中的表 ({len(db_tables)}): {sorted(db_tables)}")
        
        missing_in_db = model_tables - db_tables
        extra_in_db = db_tables - model_tables
        
        if missing_in_db:
            print(f"❌ 模型中定义但数据库中缺失的表: {missing_in_db}")
        else:
            print("✅ 所有模型表都存在于数据库中")
            
        if extra_in_db:
            print(f"⚠️  数据库中存在但模型中未定义的表: {extra_in_db}")
        else:
            print("✅ 数据库中没有多余的表")
        
        # 2. 检查表结构
        print("\n2. 表结构检查:")
        print("-" * 30)
        
        inconsistencies = []
        
        for table_name in model_tables & db_tables:
            print(f"\n检查表: {table_name}")
            
            # 获取模型定义的列
            model_table = db.metadata.tables[table_name]
            model_columns = {col.name: col for col in model_table.columns}
            
            # 获取数据库中的列
            db_columns = {col['name']: col for col in inspector.get_columns(table_name)}
            
            # 检查列
            model_col_names = set(model_columns.keys())
            db_col_names = set(db_columns.keys())
            
            missing_cols = model_col_names - db_col_names
            extra_cols = db_col_names - model_col_names
            
            if missing_cols:
                print(f"  ❌ 缺失列: {missing_cols}")
                inconsistencies.append(f"{table_name}: 缺失列 {missing_cols}")
            
            if extra_cols:
                print(f"  ⚠️  多余列: {extra_cols}")
                inconsistencies.append(f"{table_name}: 多余列 {extra_cols}")
            
            # 检查共同列的类型
            common_cols = model_col_names & db_col_names
            for col_name in common_cols:
                model_col = model_columns[col_name]
                db_col = db_columns[col_name]
                
                # 简化类型比较（SQLite的类型系统比较灵活）
                model_type = str(model_col.type).upper()
                db_type = str(db_col['type']).upper()
                
                # 处理一些常见的类型差异
                if 'VARCHAR' in model_type and 'VARCHAR' in db_type:
                    continue  # VARCHAR长度差异忽略
                if model_type == 'TEXT' and db_type == 'TEXT':
                    continue
                if model_type == 'INTEGER' and db_type == 'INTEGER':
                    continue
                if model_type == 'BOOLEAN' and db_type == 'BOOLEAN':
                    continue
                if model_type == 'DATETIME' and db_type == 'DATETIME':
                    continue
                if model_type == 'DATE' and db_type == 'DATE':
                    continue
                if model_type == 'FLOAT' and db_type == 'FLOAT':
                    continue
                    
                # 如果类型不匹配，记录差异
                if model_type != db_type:
                    print(f"  ⚠️  列 {col_name} 类型不匹配: 模型={model_type}, 数据库={db_type}")
            
            if not missing_cols and not extra_cols:
                print(f"  ✅ 表 {table_name} 结构一致")
        
        # 3. 检查外键约束
        print("\n3. 外键约束检查:")
        print("-" * 30)
        
        for table_name in model_tables & db_tables:
            model_table = db.metadata.tables[table_name]
            model_fks = model_table.foreign_keys
            db_fks = inspector.get_foreign_keys(table_name)
            
            if model_fks or db_fks:
                print(f"\n表 {table_name}:")
                print(f"  模型外键数量: {len(model_fks)}")
                print(f"  数据库外键数量: {len(db_fks)}")
                
                for fk in model_fks:
                    print(f"  模型外键: {fk.parent.name} -> {fk.column.table.name}.{fk.column.name}")
                
                for fk in db_fks:
                    print(f"  数据库外键: {fk['constrained_columns']} -> {fk['referred_table']}.{fk['referred_columns']}")
        
        # 4. 检查索引
        print("\n4. 索引检查:")
        print("-" * 30)
        
        for table_name in model_tables & db_tables:
            db_indexes = inspector.get_indexes(table_name)
            if db_indexes:
                print(f"\n表 {table_name} 的索引:")
                for idx in db_indexes:
                    print(f"  索引: {idx['name']} on {idx['column_names']} (unique: {idx['unique']})")
        
        # 5. 数据完整性检查
        print("\n5. 数据完整性检查:")
        print("-" * 30)
        
        try:
            # 检查用户表数据
            result = db.session.execute(text("SELECT COUNT(*) as count FROM users")).fetchone()
            user_count = result[0] if result else 0
            print(f"用户表记录数: {user_count}")
            
            # 检查工单表数据
            result = db.session.execute(text("SELECT COUNT(*) as count FROM work_orders")).fetchone()
            order_count = result[0] if result else 0
            print(f"工单表记录数: {order_count}")
            
            # 检查区域表数据
            result = db.session.execute(text("SELECT COUNT(*) as count FROM areas")).fetchone()
            area_count = result[0] if result else 0
            print(f"区域表记录数: {area_count}")
            
            # 检查组表数据
            result = db.session.execute(text("SELECT COUNT(*) as count FROM groups")).fetchone()
            group_count = result[0] if result else 0
            print(f"组表记录数: {group_count}")
            
        except Exception as e:
            print(f"数据完整性检查出错: {e}")
        
        # 总结
        print("\n" + "=" * 60)
        print("检查总结:")
        print("=" * 60)
        
        if not inconsistencies and not missing_in_db and not extra_in_db:
            print("✅ 数据库结构与模型定义完全一致")
        else:
            print("⚠️  发现以下不一致之处:")
            if missing_in_db:
                print(f"  - 缺失表: {missing_in_db}")
            if extra_in_db:
                print(f"  - 多余表: {extra_in_db}")
            for inconsistency in inconsistencies:
                print(f"  - {inconsistency}")
        
        print("\n检查完成!")

if __name__ == '__main__':
    try:
        check_database_consistency()
    except Exception as e:
        print(f"检查过程中出现错误: {e}")
        sys.exit(1)